package com.peiwangroup.messageservice.repository;

import com.peiwangroup.messageservice.model.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Query("SELECT o FROM Order o WHERE DATE(o.createTime) = DATE(:date)")
    List<Order> findByDate(Date date);
    
    @Query("SELECT o FROM Order o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month")
    List<Order> findByYearAndMonth(@Param("year") int year, @Param("month") int month);
    
    @Query("SELECT o FROM Order o WHERE o.createTime BETWEEN :startDate AND :endDate")
    List<Order> findByDateBetween(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
    
    @Query("SELECT o FROM Order o WHERE o.createTime BETWEEN :startTime AND :endTime")
    List<Order> findByCreateTimeBetween(@Param("startTime") Date startTime, @Param("endTime") Date endTime);
    
    @Query("SELECT o.companion as name, SUM(o.finalAmount) as amount " +
           "FROM Order o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month " +
           "GROUP BY o.companion ORDER BY amount DESC")
    List<Object[]> getMonthlyRanking(int year, int month);
}